package msr.service;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;

import net.sf.ehcache.Ehcache;
import net.sf.ehcache.Element;
import msr.cache.CacheUtil;
import msr.object.Capacity;
import msr.object.DPLN;
import msr.object.Jichiku;
import msr.object.Product;
import msr.object.Yoyaku;
import msr.util.CacheInfo;
import msr.util.ConnectDb;
import msr.util.DateParse;
import msr.util.Define;
import msr.util.ExpertCharacter;
import msr.util.ParseData;
import msr.util.QueryDefine;
import msr.util.RequestAction;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;

import com.opensymphony.xwork2.ActionContext;

/**
 * 予約、出荷、製品の各操作はこのクラスに実施される。
 * 
 * */
public class OracleService {
	private static final Logger log = Logger.getLogger(OracleService.class
			.getName());

	private static ConnectDb connectdb = ConnectDb.getInstance();
	private Ehcache cache = CacheUtil.getCache("productCache");
	UserService uService = new UserService();

	public OracleService() {
	}

	/**
	 * 予約の新規登録機能
	 * 
	 * @author tuvv
	 * 
	 * @param rtData
	 *            : yoyaku の各項目データ
	 * @return True if add success,else false
	 * 
	 * */
	@SuppressWarnings("unchecked")
	public boolean addBooking(Map<String, Object> rtData) throws SQLException {
		if (rtData != null) {
			Connection conn = connectdb.connectDatabase();
			if (conn != null) {
				try {
					// add more variable
					// execute query to db
					String query = QueryDefine.QUERY_ADD_BOOKING;
					log.info("valeu of query " + query);

					CallableStatement prepStmt = conn.prepareCall(query);

					prepStmt.registerOutParameter(1, OracleTypes.NUMBER);

					String prefcd = (String) rtData.get("prefcd");
					int kibou = Integer.parseInt((String) rtData
							.get("productexpectdate"));
					String farmcd = (String) rtData.get("farmcd");
					String getcd = (String) rtData.get("productgetcd");
					String feedcd = (String) rtData.get("productcode");
					String formcd = (String) rtData.get("productcapacity");
					int qty = Integer.parseInt((String) rtData
							.get("productquantity"));
					int weight = Integer.parseInt((String) rtData
							.get("productweight"));
					String jichikucd = (String) rtData.get("jichikucd");
					String freecomment = (String) rtData.get("productcomment");
					String kubun = (String) rtData.get("kubun_fa_yoyaku");
					Date instime = (java.sql.Date) rtData.get("ins_time");
					Date uptime = (java.sql.Date) rtData.get("up_time");
					String flag = (String) rtData.get("productshoriflag");
					String feedName = (String) rtData.get("feedName");
					String formName = (String) rtData.get("formName");

					prepStmt.setString(2, prefcd);
					prepStmt.setInt(3, kibou);
					prepStmt.setString(4, farmcd);
					prepStmt.setString(5, getcd);
					prepStmt.setString(6, feedcd);
					prepStmt.setString(7, formcd);
					prepStmt.setInt(8, qty);
					prepStmt.setInt(9, weight);
					prepStmt.setString(10, jichikucd);
					prepStmt.setString(11, freecomment);
					prepStmt.setString(12, kubun);
					prepStmt.setDate(13, instime);
					prepStmt.setDate(14, uptime);
					prepStmt.setString(15, flag);
					prepStmt.execute();
					int res = prepStmt.getInt(1);
					log.info("value of ref after insert a new booking record "
							+ res);
					if (res > 0) {
						/*
						 * put data into cache
						 */
						DateParse dParse = new DateParse();
						String now = dParse.getNow();
						boolean overtime = dParse.compareDate(now,
								String.valueOf(kibou));
						Yoyaku y = new Yoyaku(res, prefcd,
								ParseData.parseDate(String.valueOf(kibou)),
								farmcd, getcd, feedcd, formcd, qty, weight,
								jichikucd, freecomment, kubun, instime, uptime,
								Define.NEW, feedName, formName, overtime);
						// put data into yoyaku object
						Element e = cache.get(CacheInfo.YOYAKU_LIST);
						List<Yoyaku> listYoyaku = new ArrayList<Yoyaku>();
						boolean check = false;
						if (e != null) {
							log.info("yoyaku list cache in not empty");
							listYoyaku = (List<Yoyaku>) e.getObjectValue();
						} else {
							log.info("yoyaku list cache is empty");
							Map<String, String> params = new HashMap<String, String>();
							listYoyaku = this.getListYoyaku(params,
									RequestAction.GET_ALL_YOYAKU);
							if (listYoyaku != null)
								check = true;
						}
						if (!check) {
							log.info("add new record into list cache");
							listYoyaku.add(y);
							e = new Element(CacheInfo.YOYAKU_LIST, listYoyaku);
							cache.put(e);
						}
						return true;
					} else
						return false;
				} catch (SQLException ex) {
					log.info("sqlexception " + ex.getMessage());
					ex.printStackTrace();
				}
			} else
				return false;
		} else
			return false;
		return true;
	}

	/**
	 * 製品を検索する機能
	 * 
	 * @param params
	 *            : kindの値はFEED_NAMEの場合、paramsは製品名です。
	 *            ：kindはFEED_CDの場合、params製品コードの値です。
	 * @param kind
	 *            : control parameter :1.GET_ALL_PRODUCT すべての製品を引き出す 2.FEED_NAME
	 *            : パラメターとする製品名で検索する。 3.FEED_CD パラメターとする製品コードで検索する。
	 * 
	 * @return list match with this condition
	 * */
	@SuppressWarnings("unchecked")
	public List<Product> searchProduct(String params, String kind) {
		// using pipeline to get data
		List<Product> rtList = new ArrayList<Product>();
		if (params != null) {
			// get product list from cache
			Element productList = cache.get(CacheInfo.PRODUCT_LIST);
			if (productList == null) {
				log.info("cache is empty and loading from database");
				Connection conn = connectdb.connectDatabase();
				if (conn != null) {
					try {
						String sql = "";
						if (kind.equals(RequestAction.GET_ALL_PRODUCT)) {
							log.info("get all product with no condition");
							sql = QueryDefine.QUERY_PRODUCT;
						} else
							sql = QueryDefine.QUERY_PRODUCT_BY;
						CallableStatement vStatement = conn.prepareCall(sql);
						vStatement.registerOutParameter(1, OracleTypes.CURSOR);
						if (kind.equals(RequestAction.FEED_NAME)) {
							vStatement.setString(2, QueryDefine.PRODUCT_NAME);
							log.info("get product by  name" + params);
							vStatement.setString(3, '%' + params + '%');
						} else if (kind.equals(RequestAction.FEED_CD)) {
							vStatement.setString(2, QueryDefine.PRODUCT_CD);
							vStatement.setString(3, '%' + params + '%');
							log.info("get product by feedcd " + params);
						}
						// execute sql query after add params into query string
						vStatement.execute();
						ResultSet re = ((OracleCallableStatement) vStatement)
								.getCursor(1);
						if (re != null) {
							while (re.next()) {
								Product p = new Product();
								p.setName(re.getString("FEED_NAME"));
								p.setCode(re.getString("FEED_CD"));
								p.setType(re.getString("S_FORM_CD"));
								p.setGetCD(re.getString("GET_CD"));
								p.setFeedKana(ExpertCharacter.convertContent(re
										.getString("FEED_NAME")));
								rtList.add(p);
							}
							/*
							 * put data into cache : {key,value} =
							 * {PRODUCT_LIST,return list product}
							 */
							log.info("put data into cache. product list has size "
									+ rtList.size());
							Element e = new Element(CacheInfo.PRODUCT_LIST,
									rtList);
							cache.put(e);
							log.info("cache updated!");
						} else {
							log.info("result set is null");
							/*
							 * TODO need throw expertexception at here
							 */
							return null;
						}
						vStatement.close();
					} catch (SQLException ex) {
						log.info("sqlexception " + ex.getMessage());
						ex.printStackTrace();
					}

				} else {
					log.info("Connection was refused");
					/*
					 * TODO need throw experrtexception at here
					 */
					return null;
				}
			} else {
				// data existing in cache
				log.info("now cache has data and starting get data in it");
				rtList = (List<Product>) productList.getObjectValue();
				if (kind.equals(RequestAction.GET_ALL_PRODUCT)) {
					// get all product
					log.info("get all list product that no condition. that list has size "
							+ rtList.size());
					return rtList;
				} else if (kind.equals(RequestAction.FEED_NAME)) {
					// get list prsoduct by name
					log.info("get list product with condition is must matching feed name");
					List<Product> listProductName = this.getListProductByName(
							params, rtList);
					log.info("size of getted list " + listProductName.size());
					return listProductName;
				}
			}
		} else {
			/*
			 * TODO need throw experrtexception at here
			 */
			// ExpertException ex = new ExpertException();
			// throw new NullException(ex);
			return null;
		}
		return rtList;
	}

	/**
	 * 予約一覧を取得する機能。 『予約一覧』画面にそのリストを表示する。
	 * 
	 * @param params
	 *            : MAP　データ。 Kindの値によって、パラメター渡しも違う。
	 * @param kind
	 *            : 1.GET_ALL_YOYAKU すべての予約リスト. 2.GET_CUSTOM_YOYAKU :
	 *            製品コードなどで検索する。
	 * @return List<(@link YOYAKU}>
	 * */
	@SuppressWarnings("unchecked")
	public List<Yoyaku> getListYoyaku(Map<String, String> params, String kind) {
		List<Yoyaku> rtList = new ArrayList<Yoyaku>();
		if (params != null) {
			// get data from cache
			Element yoyakuList = cache.get(CacheInfo.YOYAKU_LIST);
			if (yoyakuList == null) {
				log.info("now yoyaku cache is empty and  loading data from database");
				Connection conn = connectdb.connectDatabase();
				if (conn != null) {
					try {
						String sql = "";
						// control by condition
						if (kind.equals(RequestAction.GET_ALL_YOYAKU)) {
							log.info("get list  yoyaku that no condition");
							sql = QueryDefine.QUERY_ALL_YOYAKU;
						} else if (kind.equals(RequestAction.GET_CUSTOM_YOYAKU)) {
							log.info("get list yoyaku by condition ");
							sql = QueryDefine.QUERY_YOYAKU;
						}
						log.info("value of query string " + sql);
						CallableStatement vStatement = conn.prepareCall(sql);
						vStatement.registerOutParameter(1, OracleTypes.CURSOR);
						if (kind.equals(RequestAction.GET_CUSTOM_YOYAKU)) {
							String feedCd = params.get("feedCD");
							vStatement.setString(2, '%' + feedCd + '%');
						}
						vStatement.execute();
						ResultSet re = ((OracleCallableStatement) vStatement)
								.getCursor(1);
						if (re != null) {
							while (re.next()) {
								DateParse dParse = new DateParse();
								String now = dParse.getNow();
								String kibou = String.valueOf(re
										.getInt("KIBOU_YMD"));
								boolean overDate = dParse.compareDate(now,
										kibou);
								Yoyaku p = new Yoyaku(re.getInt("SEQ_NO"),
										re.getString("PREF_CD"),
										ParseData.parseDate(String.valueOf(re
												.getInt("KIBOU_YMD"))),
										re.getString("FARM_CD"),
										re.getString("GET_CD"),
										re.getString("FEED_CD"),
										re.getString("FORM_CD"),
										re.getInt("QTY"), re.getInt("WEIGHT"),
										re.getString("JICHIKUCD"),
										re.getString("FREE_COMMENT"),
										re.getString("KUBUN_FA_YOYAKU"),
										re.getDate("INS_TIME"),
										re.getDate("UP_TIME"),
										re.getString("SHORI_FLG"),
										re.getString("FEED_NAME"),
										re.getString("FROM_NAME"), overDate);
								rtList.add(p);
							}
							/*
							 * add data into cache {key,value} =
							 * {YOYAKU_LIST,return list yoyaku}
							 */
							log.info("put data into cache : YOYAKU");
							Element e = new Element(CacheInfo.YOYAKU_LIST,
									rtList);
							cache.put(e);
							log.info("cache updated!");
						} else {
							log.info("result set is null");
							/*
							 * TODO need throw expertexception
							 */
							return null;
						}
					} catch (SQLException ex) {
						log.info("sqlexception " + ex.getMessage());
						ex.printStackTrace();
					}
				}
			} else {
				log.info("list yoyaku cache has data, and starting get it");
				rtList = (List<Yoyaku>) yoyakuList.getObjectValue();
				if (kind.equals(RequestAction.GET_ALL_YOYAKU)) {
					log.info("get all yoyaku and is has size " + rtList.size());
					return rtList;
				} else if (kind.equals(RequestAction.GET_CUSTOM_YOYAKU)) {
					String feedcd = params.get("feedCD");
					log.info("search yoyaku by feedcd" + feedcd);
					List<Yoyaku> tmpList = this.searchYoyakuInList(feedcd,
							rtList);
					log.info("size of list result " + tmpList.size());
					return tmpList;
				}
			}
		} else {
			log.info("params is null ");
			/*
			 * TODO need throw expertexception
			 */
			return null;
		}
		return rtList;
	}

	/**
	 * 容量リストを取得する機能。 ここにキャッシュを使わない。
	 * 
	 * @author tuvv
	 * @param productcapacity
	 *            : 予約コード
	 * @return List<{@link Capacity} >
	 * */
	public List<Capacity> getListCapacity(String productcapacity) {
		List<Capacity> rtList = new ArrayList<Capacity>();
		if (productcapacity != null) {
			Connection conn = connectdb.connectDatabase();
			if (conn != null) {
				try {
					String sql = QueryDefine.QUERY_SHIP_BY;
					CallableStatement vStatement = conn.prepareCall(sql);
					vStatement.registerOutParameter(1, OracleTypes.CURSOR);
					vStatement.setString(2, productcapacity);
					vStatement.execute();
					ResultSet re = ((OracleCallableStatement) vStatement)
							.getCursor(1);
					if (re != null) {
						while (re.next()) {
							Capacity cp = new Capacity(re.getString("cd"),
									re.getString("name"), re.getString("type"),
									re.getInt("weight"));
							rtList.add(cp);
						}
					} else {
						log.info("result set is null");
						/*
						 * TODO need throw expertexception
						 */
						return null;
					}
				} catch (SQLException ex) {
					log.info("sqlexception " + ex.getMessage());
					ex.printStackTrace();
				}
			}
		}
		return rtList;
	}

	/**
	 * 軒先を取得する機能
	 * 
	 * @param kind
	 *            : control params - 今、使ってない
	 * @param jichikucd
	 *            :　分場のコード
	 * @return value of nokisaki. -1 if not exist
	 * */
	public int getNokisaki(String kind, String jichikucd) {
		int rtvalue = -1;
		Connection conn = connectdb.connectDatabase();
		if (conn != null) {
			try {
				String sql = null;
				sql = QueryDefine.QUERY_NOKISAKI_NEW;
				Map<String, Object> session = ActionContext.getContext()
						.getSession();
				String username = (String) session.get("username");
				CallableStatement vStatement = conn.prepareCall(sql);
				vStatement.registerOutParameter(1, OracleTypes.CURSOR);
				vStatement.setString(2, username);
				vStatement.setString(3, jichikucd);
				vStatement.execute();
				ResultSet re = ((OracleCallableStatement) vStatement)
						.getCursor(1);

				if (re != null) {
					while (re.next()) {
						rtvalue = re.getInt("NOKISAKI");
						log.info("value of nokisaki " + rtvalue);
					}
				} else {
					log.info("result set is null");
					/*
					 * TODO need throw expertexception
					 */
					return rtvalue;
				}

			} catch (SQLException ex) {
				log.info("sqlexception " + ex.getMessage());
				ex.printStackTrace();
			}
		}
		return rtvalue;
	}

	/**
	 * 容量のタイプをチェックする機能 。パターンによって、チェックする。{@link RequestAction}の各パターンに参考
	 * 
	 * @param productcapacity
	 *            :容量のコード
	 * @return matched type
	 * 
	 * */
	public String checkType(String productcapacity) {
		String type = null;
		if (productcapacity != null) {
			String tmp = productcapacity.trim();
			if (tmp.matches(RequestAction.BARA_PATTERN))
				return RequestAction.BARA_TYPE;
			else if (tmp.matches(RequestAction.TB_PATTERN))
				return RequestAction.TB_TYPE;
			else if (tmp.matches(RequestAction.KAMIBUKURO_PATTERN))
				return RequestAction.KAMIBUKURO_TYPE;
			else
				return RequestAction.OTHER_TYPE;
		}
		return type;
	}

	/**
	 * 分場一覧を取得する機能。 ここにキャッシュを使わない。
	 * 
	 * @param params
	 *            :kindの値によって、パラメターも違う。
	 * @param kind
	 *            : コントロールパラ
	 * @return {@link Jichiku}
	 * */

	public List<Jichiku> getListJichiku(Map<String, Object> params, String kind) {
		List<Jichiku> rtData = new ArrayList<Jichiku>();
		if (params != null) {
			Connection conn = connectdb.connectDatabase();
			if (conn != null) {
				try {
					String sql = null;
					if (kind.equals(RequestAction.GET_ALL_JICHIKU_BY_USER)) {
						log.info("get list jichiku by user");
						sql = QueryDefine.QUERY_JICHIKU_BY_USER;
					} else if (kind
							.equals(RequestAction.GET_ALL_JICHIKU_BY_DPLN)) {
						log.info("get list jichiku by dpln");
						sql = QueryDefine.QUERY_JICHIKU_BY_DPLN;
					}
					CallableStatement vStatement = conn.prepareCall(sql);
					vStatement.registerOutParameter(1, OracleTypes.CURSOR);
					if (kind.equals(RequestAction.GET_ALL_JICHIKU_BY_USER)) {
						String username = (String) params.get("username");
						log.info("value of username " + username);
						vStatement.setString(2, username);
					} else if (kind
							.equals(RequestAction.GET_ALL_JICHIKU_BY_DPLN)) {
						int seqno = (int) params.get("seqno");
						log.info("value of seqno " + seqno);
						vStatement.setInt(2, seqno);
					}
					vStatement.execute();
					ResultSet re = ((OracleCallableStatement) vStatement)
							.getCursor(1);

					if (re != null) {
						while (re.next()) {
							Jichiku ji = new Jichiku(re.getString("cd"),
									re.getString("name"));
							rtData.add(ji);
						}
						log.info("size of list jichiku " + rtData.size());
					}
				} catch (SQLException ex) {
					log.info("sqlexception " + ex.getMessage());
					ex.printStackTrace();
				}
			}
		} else {
			log.info("params is null ");
			/*
			 * TODO need throw via expertexception
			 */
			return null;
		}
		return rtData;
	}

	/**
	 * 出荷リストを取得する機能. キャッシュを使う.
	 * 
	 * 
	 * @param params
	 *            :kindの値によって、パラも違う
	 * @param kind
	 *            : コントロールパラ
	 * @return {@link DPLN}
	 * */
	@SuppressWarnings("unchecked")
	public List<DPLN> getListDpln(Map<String, Object> params, String kind) {
		List<DPLN> rtData = new ArrayList<DPLN>();
		Connection conn = connectdb.connectDatabase();
		Map<String, Object> session = ActionContext.getContext().getSession();
		String username = (String) session.get("username");

		String upuser = uService.getUpUserByUsername(username);
		log.info("value of upuser " + upuser);

		// get data from cache
		Element dplnlist = cache.get(CacheInfo.DPLN_LIST);
		int date = (int) params.get("date");
		int supdate = 0;
		String feedcd = (String) params.get("feedcd");
		log.info("value of date and feedcd " + date + "/" + feedcd);
		if (dplnlist == null) {
			log.info("dpln list is not exist in cache, then load from database");
			if (conn != null) {
				try {
					String sql = "";
					int type = 0;
					if (params.get("supdate") != null)
						supdate = Integer.parseInt((String) params
								.get("supdate"));
					if (kind.equals(RequestAction.GET_ALL_DPLN)) {
						log.info("get list dpln that no condition");
						sql = QueryDefine.QUERY_GET_LIST_DPLN;
					} else if (kind.equals(RequestAction.GET_CUSTOM_DPLN)) {
						log.info("get list dpln with condition feedcd or supdate");
						if (supdate == 0 && feedcd != null) {
							sql = QueryDefine.QUERY_GET_LIST_DPLN_FEEDCD;
							type = 1;
						} else if (supdate != 0
								&& (feedcd == null || feedcd.equals(""))) {
							sql = QueryDefine.QUERY_GET_LIST_DPLN_SUPDATE;
							type = 2;
						} else if (supdate != 0 && feedcd != null) {
							sql = QueryDefine.QUERY_GET_LIST_DPLN_CUSTOM;
							type = 3;
						} else {
							log.info("in the furture will use!value of feedcd "
									+ feedcd);
						}
					}
					CallableStatement vStatement = conn.prepareCall(sql);
					log.info("value of sql " + sql);
					vStatement.registerOutParameter(1, OracleTypes.CURSOR);
					vStatement.setString(2, upuser);
					vStatement.setInt(3, date);

					if (kind.equals(RequestAction.GET_CUSTOM_DPLN)) {
						if (type == 1) {
							vStatement.setString(4, '%' + feedcd + '%');
						} else if (type == 2)
							vStatement.setString(4,
									'%' + String.valueOf(supdate) + '%');
						else if (type == 3) {
							vStatement.setString(4,
									'%' + String.valueOf(supdate) + '%');
							vStatement.setString(5, '%' + feedcd + '%');
						}
					}
					vStatement.execute();
					ResultSet rs = ((OracleCallableStatement) vStatement)
							.getCursor(1);
					if (rs != null && rs.next()) {
						while (rs.next()) {
							DPLN dp = new DPLN(
									rs.getInt("SEQ_NO"),
									rs.getString("PREF_CD"),
									rs.getString("ORDNO"),
									rs.getInt("GYOU_NO"),
									ParseData.parseDate(rs.getString("SUPDATE")),
									rs.getInt("KIBOU_YMD"), rs
											.getString("FEED_CD"), rs
											.getString("FORM_CD"), rs
											.getString("GET_CD"), rs
											.getString("BASE_CD"), rs
											.getInt("QTY"),
									rs.getInt("WEIGHT"), rs
											.getString("JIKO_KIND"), rs
											.getString("S_JIKI_CD"), rs
											.getString("FEED_NAME"), rs
											.getString("FROM_NAME"), rs
											.getInt("NOKISAKI"), rs
											.getString("JICHIKUCD"), rs
											.getString("JICHIKU_NAME"), rs
											.getString("FARM_CD"));
							rtData.add(dp);
						}
						/*
						 * put data into cache
						 */
						log.info("put data into cache");
						Element e = new Element(CacheInfo.DPLN_LIST, rtData);
						cache.put(e);
						log.info("update cache!");
					} else {
						log.info("result is empty");
						/*
						 * TODO need throw expertexception
						 */
						return null;
					}
				} catch (SQLException ex) {
					log.info("sqlexception " + ex.getMessage());
					ex.printStackTrace();
				}
			} else {
				log.info("Connection is refuse!");
				/*
				 * TODO need throw expertexception
				 */
				return null;
			}
		} else {
			log.info("dpln list is existing in cache and getting it");
			rtData = (List<DPLN>) dplnlist.getObjectValue();
			if (kind.equals(RequestAction.GET_ALL_DPLN)) {
				log.info("get list dpln that no condition and list has size "
						+ rtData.size());
				return rtData;
			} else if (kind.equals(RequestAction.GET_CUSTOM_DPLN)) {

				List<DPLN> tmpListDpln = this.getListDplnCustom(supdate,
						feedcd, rtData);
				log.info("get list dpln by condition and list has size "
						+ tmpListDpln.size());
				return tmpListDpln;
			}
		}
		return rtData;
	}

	/**
	 * 予約レコード変更機能 キャッシュを使わない
	 * 
	 * @param params
	 *            : 予約オブジェクトの必要な項目
	 * @param : kind : コントロールパラ
	 * @return true if success
	 * */
	@SuppressWarnings("unchecked")
	public boolean editYoyaku(Map<String, Object> params, String kind) {
		// TODO Auto-generated method stub
		boolean check = false;
		if (params != null) {
			Connection conn = connectdb.connectDatabase();
			if (conn != null) {
				try {
					// add more variable
					// execute query to db
					if (kind.equals(RequestAction.UPDATE_YOYAKU_FULL)) {
						String query = QueryDefine.UPDATE_YOYAKU;
						int seqno = (int) params.get("seqNo");
						String formcd = (String) params.get("formCD");
						int qty = (int) params.get("qty");
						int weight = (int) params.get("weight");
						String jichikucd = (String) params.get("jichikuCD");
						String freecmt = (String) params.get("freeComment");
						String kubun = (String) params.get("kubunFaYoyaku");
						Date uptime = (java.sql.Date) params.get("uptime");
						String flag = (String) params.get("shori_flg");
						String kibou = (String) params.get("kibouYMD");

						CallableStatement cstmt = conn.prepareCall(query);
						cstmt.setString(1, formcd);
						cstmt.setInt(2, qty);
						cstmt.setInt(3, weight);
						cstmt.setString(4, jichikucd);
						cstmt.setString(5, freecmt);
						cstmt.setString(6, kubun);
						cstmt.setDate(7, uptime);
						cstmt.setString(8, flag);
						cstmt.setInt(9, Integer.parseInt(kibou));
						cstmt.setInt(10, seqno);
						int res = cstmt.executeUpdate();

						log.info("value of ref after update record " + res);
						if (res > 0) {
							/*
							 * put data into cache
							 */
							// put data into yoyaku object
							Element e = cache.get(CacheInfo.YOYAKU_LIST);
							List<Yoyaku> listYoyaku = new ArrayList<Yoyaku>();
							if (e != null) {
								listYoyaku = (List<Yoyaku>) e.getObjectValue();
								int index = this.iterateYoyaku(seqno,
										listYoyaku);
								// update new value of fields for yoyaku object
								log.info("index match " + index);
								if (index > -1) {
									listYoyaku.get(index).setFormCD(formcd);
									listYoyaku.get(index).setQty(qty);
									listYoyaku.get(index).setWeight(weight);
									listYoyaku.get(index).setJichikuCD(
											jichikucd);
									listYoyaku.get(index).setFreeComment(
											freecmt);
									listYoyaku.get(index).setKubunFaYoyaku(
											kubun);
									listYoyaku.get(index).setUpTime(uptime);
									listYoyaku.get(index).setShoriFlg(flag);
									listYoyaku.get(index).setKibouYMD(
											ParseData.parseDate(kibou));
								}

							}
							// update list cache
							log.info("update yoyaku list cache ");
							e = new Element(CacheInfo.YOYAKU_LIST, listYoyaku);
							cache.put(e);
							log.info("cache updated!");
							return true;
						} else {
							log.info("result set is null");
							/*
							 * TODO need throw expertexception at here
							 */
							return false;
						}
					}
				} catch (SQLException ex) {
					log.info("sqlexception " + ex.getMessage());
					ex.printStackTrace();
				}
			} else
				return check;
		} else
			return check;
		return check;
	}

	/**
	 * Function: 予約削除実行
	 * 
	 * @param params
	 * @return true if success, otherwise is false
	 * */
	@SuppressWarnings("unchecked")
	public boolean deleteYoyaku(Map<String, Object> params) {
		// TODO Auto-generated method stub
		boolean check = false;
		if (params != null) {
			// Connection conn = this.connectDatabase();
			Connection conn = connectdb.connectDatabase();
			if (conn != null) {
				try {
					// add more variable
					// execute query to db
					String query = QueryDefine.DELETE_YOYAKU;
					log.info("valeu of query " + query);
					int seqno = (int) params.get("seqNo");
					CallableStatement cstmt = conn.prepareCall(query);
					cstmt.setString(1, (String) params.get("kubunFaYoyaku"));
					cstmt.setDate(2, (java.sql.Date) params.get("uptime"));
					cstmt.setInt(3, seqno);

					int result = cstmt.executeUpdate();
					log.info("value of result " + result);
					if (result > 0) {
						/*
						 * delete data into cache
						 */
						Element e = cache.get(CacheInfo.YOYAKU_LIST);
						List<Yoyaku> listYoyaku = new ArrayList<Yoyaku>();
						if (e != null) {
							listYoyaku = (List<Yoyaku>) e.getObjectValue();
							int index = this.iterateYoyaku(seqno, listYoyaku);
							// update new value of fields for yoyaku object
							log.info("index match " + index);
							if (index > -1) {
								listYoyaku.remove(index);
								e = new Element(CacheInfo.YOYAKU_LIST,
										listYoyaku);
								cache.put(e);
							}
							// esle need ref to exception control
						}
						check = true;
					}
				} catch (SQLException ex) {
					log.info("sqlexception " + ex.getMessage());
					ex.printStackTrace();
				}
			} else
				return check;
		} else
			return check;
		return check;
	}

	public String getCapacity(String feedCD) {
		// TODO Auto-generated method stub
		String capacity = null;
		if (feedCD != null) {
			// Connection conn = this.connectDatabase();
			Connection conn = connectdb.connectDatabase();
			if (conn != null) {
				try {
					// add more variable
					// execute query to db
					String query = QueryDefine.QUERY_PRODUCT_CAPACITY;
					log.info("valeu of query " + query);
					CallableStatement vStatement = conn.prepareCall(query);
					vStatement.registerOutParameter(1, OracleTypes.CURSOR);
					vStatement.setString(2, feedCD);
					vStatement.execute();
					ResultSet rs = ((OracleCallableStatement) vStatement)
							.getCursor(1);
					while (rs.next()) {
						capacity = rs.getString("S_FORM_CD");
					}
				} catch (SQLException ex) {
					log.info("sqlexception " + ex.getMessage());
					ex.printStackTrace();
				}
			} else
				return null;
		} else
			return null;
		return capacity;
	}

	/**
	 * get list product by name
	 * 
	 * @param name
	 *            : feed name
	 * @param listProduct
	 *            : getted list product
	 * @return List{@link Product} after matching
	 */
	private List<Product> getListProductByName(String name,
			List<Product> listProduct) {
		List<Product> rtList = new ArrayList<Product>();
		Iterator<Product> iterator = listProduct.iterator();
		while (iterator.hasNext()) {
			Product p = iterator.next();
			if (p.getFeedKana() != null) {
				String feedKana = p.getFeedKana();
				CharSequence tmpName = ExpertCharacter.convertContent(name);
				if (feedKana.trim().contains(tmpName))
					rtList.add(p);
			}
		}
		return rtList;
	}

	public List<Yoyaku> searchYoyakuInList(String feedcd,
			List<Yoyaku> listYoyaku) {
		List<Yoyaku> rtList = new ArrayList<Yoyaku>();
		Iterator<Yoyaku> iterator = listYoyaku.iterator();
		while (iterator.hasNext()) {
			Yoyaku y = iterator.next();
			if (y.getFeedCD() != null) {
				CharSequence tmpFeedCd = ExpertCharacter.convertContent(feedcd);
				if (y.getFeedCD().trim().contains(tmpFeedCd))
					rtList.add(y);
			} else
				// throw exception
				log.info("feedcd is null");
		}
		return rtList;
	}

	/**
	 * get list yoyaku by feedcd . this function is not use at the moment
	 * */
	public List<Yoyaku> getListYoyakuByFeedCd(String feedcd,
			List<Yoyaku> listYoyaku) {
		List<Yoyaku> rtList = new ArrayList<Yoyaku>();
		Iterator<Yoyaku> iterator = rtList.iterator();
		while (iterator.hasNext()) {
			Yoyaku y = iterator.next();
			/*
			 * To do need regrex string to match product name
			 */
			if (y.getFeedCD().equals(feedcd))
				rtList.add(y);
		}
		return rtList;
	}

	/**
	 * 条件によって、出荷リストがマッチングする機能
	 * 
	 * @param supdate
	 *            :出荷希望日
	 * @param feedcd
	 *            :製品コード
	 * @param listDpln
	 *            :返した出荷リスト
	 * @return List<{@link DPLN}>
	 * */
	private List<DPLN> getListDplnCustom(int supdate, String feedcd,
			List<DPLN> listDpln) {
		List<DPLN> rtList = new ArrayList<DPLN>();
		Iterator<DPLN> iterator = listDpln.iterator();
		while (iterator.hasNext()) {
			DPLN d = iterator.next();
			if (supdate == 0 && feedcd != null) {
				if (d.getFeedcd() != null) {
					CharSequence tmpFeedCd = ExpertCharacter
							.convertContent(feedcd);
					if (d.getFeedcd().trim().contains(tmpFeedCd))
						rtList.add(d);
				}
			} else if (supdate != 0 && (feedcd == null || feedcd.equals(""))) {
				if (d.getSupdate() != null) {
					CharSequence tmpSupdate = ExpertCharacter
							.convertContent(String.valueOf(supdate));
					if (d.getSupdate().trim().contains(tmpSupdate))
						rtList.add(d);
				}
			} else if (supdate != 0 && feedcd != null) {
				if (d.getSupdate() != null && d.getFeedcd() != null) {
					CharSequence tmpSupdate = ExpertCharacter
							.convertContent(String.valueOf(supdate));
					CharSequence tmpFeedcd = ExpertCharacter
							.convertContent(feedcd);
					if (d.getSupdate().trim().contains(tmpSupdate)
							&& d.getFeedcd().trim().contains(tmpFeedcd))
						rtList.add(d);
				}
			}
		}
		return rtList;
	}

	/**
	 * SeqNoで予約レコードを検索する機能
	 * 
	 * @param seqno
	 *            :予約マスターのプライマリーキー
	 * @return {@link Yoyaku} object
	 * */
	public Yoyaku getYoyakuBySeqno(int seqno) {
		Yoyaku p = null;
		Connection conn = connectdb.connectDatabase();
		if (conn != null) {
			try {
				String sql = "";
				sql = QueryDefine.QUERY_YOYAKU_BY_SEQ;
				log.info("value of sql " + sql);
				CallableStatement vStatement = conn.prepareCall(sql);
				vStatement.registerOutParameter(1, OracleTypes.CURSOR);
				vStatement.setInt(2, seqno);
				vStatement.execute();
				ResultSet re = ((OracleCallableStatement) vStatement)
						.getCursor(1);
				if (re != null) {
					while (re.next()) {
						DateParse dParse = new DateParse();
						String now = dParse.getNow();
						String kibou = String.valueOf(re.getInt("KIBOU_YMD"));
						boolean overDate = dParse.compareDate(now, kibou);
						p = new Yoyaku(re.getInt("SEQ_NO"),
								re.getString("PREF_CD"),
								ParseData.parseDate(String.valueOf(re
										.getInt("KIBOU_YMD"))),
								re.getString("FARM_CD"),
								re.getString("GET_CD"),
								re.getString("FEED_CD"),
								re.getString("FORM_CD"), re.getInt("QTY"),
								re.getInt("WEIGHT"), re.getString("JICHIKUCD"),
								re.getString("FREE_COMMENT"),
								re.getString("KUBUN_FA_YOYAKU"),
								re.getDate("INS_TIME"), re.getDate("UP_TIME"),
								re.getString("SHORI_FLG"),
								re.getString("FEED_NAME"),
								re.getString("FROM_NAME"), overDate);
					}
				} else {
					log.info("result set is null");
					return null;
				}
			} catch (SQLException ex) {
				log.info("sqlexception " + ex.getMessage());
				ex.printStackTrace();
			}
		}
		return p;
	}

	/**
	 * iterate yoyaku list
	 * 
	 * @param seqno
	 *            :予約マスターのプライマリーキー
	 * @param listYoyaku
	 *            : 返した予約一覧
	 * @return index of matched record
	 * */
	private int iterateYoyaku(int seqno, List<Yoyaku> listYoyaku) {
		int index = -1;
		Iterator<Yoyaku> it = listYoyaku.iterator();
		int i = 0;
		while (it.hasNext()) {
			Yoyaku tmp = it.next();
			if (tmp.getSeqNo() == seqno)
				index = i;
			i++;
		}
		return index;
	}
}
